from src.model.models import *
from src.db_init import init


# 数据库驱动类型维表
def read_dim_db_driver_type(db_driver_name=None):
    if db_driver_name:
        table_info = DimDbDriverType.query.filter_by(db_driver_name=db_driver_name).first()
        return True if table_info else False
    else:
        table_info = DimDbDriverType.query.join(User, User.id == DimDbDriverType.owner_id). \
            add_columns(DimDbDriverType.id, DimDbDriverType.db_driver_name, DimDbDriverType.db_driver_describe,
                        DimDbDriverType.owner_id, User.name).all()
        arr = [{
            'id': data.id,
            'db_driver_name': data.db_driver_name,
            'db_driver_describe': data.db_driver_describe,
            'owner_id': data.owner_id,
            'owner_name': data.name
        } for data in table_info]

        return arr


# 数据类型维表
def read_dim_data_type(data_type_name=None, db_driver_id=None):
    if data_type_name and db_driver_id:
        table_info = DimDataType.query.filter_by(data_type_name=data_type_name, db_driver_id=db_driver_id).first()
        return True if table_info else False
    else:
        query_info = DimDataType.query.join(DimDbDriverType, DimDbDriverType.id == DimDataType.db_driver_id). \
            join(User, User.id == DimDataType.owner_id). \
            add_columns(DimDataType.id, DimDataType.data_type_name, DimDataType.data_type_describe,
                        DimDataType.db_driver_id, DimDbDriverType.db_driver_name, DimDataType.owner_id, User.name). \
            filter(DimDataType.is_valid == 1)
        if db_driver_id:
            query_info = query_info.filter(DimDataType.db_driver_id == db_driver_id)

        table_info = query_info.all()
        if table_info:
            arr = [{
                'id': data.id,
                'data_type_name': data.data_type_name,
                'data_type_describe': data.data_type_describe,
                'db_driver_id': data.db_driver_id,
                'db_driver_name': data.db_driver_name,
                'owner_id': data.owner_id,
                'owner_name': data.name
            } for data in table_info]

            return arr


# 数据库维表
def read_dim_db(db_name=None, db_driver_id=None, product_id=None):
    if product_id and db_name and db_driver_id:
        table_info = DimDb.query.filter_by(db_name=db_name, db_driver_id=db_driver_id, product_id=product_id).first()
        return True if table_info else False
    else:
        table_info = DimDb.query.filter_by(is_valid=1).all()
        arr = [{
            'id': data.id,
            'db_name': data.db_name,
            'db_describe': data.db_describe,
            'db_driver_id': data.db_driver_id,
            'owner_id': data.owner_id
        } for data in table_info]

        return arr


# 指定产品的所有库
def read_product_db(product_id=None):
    if product_id:
        table_info = DimDb.query.join(DimDbDriverType, DimDbDriverType.id == DimDb.db_driver_id). \
            join(User, User.id == DimDb.owner_id). \
            add_columns(DimDb.id, DimDb.db_name, DimDb.db_driver_id, DimDb.db_describe, DimDb.owner_id,
                        DimDbDriverType.db_driver_name, User.name).\
            filter(DimDb.product_id == product_id).\
            filter(DimDb.is_valid == 1).all()
        return [{
            'id': data.id,
            'db_name': data.db_name,
            'db_describe': data.db_describe,
            'db_driver_id': data.db_driver_id,
            'db_driver_name': data.db_driver_name,
            'owner_id': data.owner_id,
            'owner_name': data.name
        } for data in table_info]
    else:
        table_info = DimDb.query.join(DimDbDriverType, DimDbDriverType.id == DimDb.db_driver_id). \
            add_columns(DimDb.id, DimDb.db_name, DimDb.db_driver_id, DimDb.db_describe, DimDb.owner_id,
                        DimDbDriverType.db_driver_name) \
            .filter(DimDb.is_valid == 1).all()
        return [{
            'id': data.id,
            'db_name': data.db_name,
            'db_describe': data.db_describe,
            'db_driver_id': data.db_driver_id,
            'db_driver_name': data.db_driver_name,
            'owner_id': data.owner_id
        } for data in table_info]


# 指定库的所有表
def read_db_table_relate(db_id, table_name=None):
    if table_name:
        table_info = DbTableRelate.query.filter_by(db_id=db_id, table_name=table_name).first()
        return True if table_info else False
    else:
        table_info = DbTableRelate.query.join(User, User.id == DbTableRelate.owner_id).\
            add_columns(DbTableRelate.id, DbTableRelate.db_id, DbTableRelate.table_name, DbTableRelate.table_describe,
                        DbTableRelate.table_info, DbTableRelate.owner_id, User.name).\
            filter(DbTableRelate.db_id == db_id).filter(DbTableRelate.is_valid == 1).all()
        arr = [{
            'id': data.id,
            'db_id': data.db_id,
            'table_name': data.table_name,
            'table_describe': data.table_describe,
            'table_info': data.table_info,
            'owner_id': data.owner_id,
            'owner_name': data.name
        } for data in table_info]

        return arr


# 指定表的所有字段
def read_table_column_relate(table_id, db_id=None, column_id=None):
    if column_id:
        if db_id:
            table_info = TableColumnRelate.query.filter_by(db_id=db_id, table_id=table_id,
                                                           column_id=column_id).first()
        else:
            table_info = TableColumnRelate.query.filter_by(table_id=table_id, column_id=column_id).first()
        return True if table_info else False
    else:
        if db_id:
            table_info = TableColumnRelate.query.join(DimColumn, DimColumn.id == TableColumnRelate.column_id). \
                join(DimDataType, DimDataType.id == DimColumn.data_type_id). \
                add_columns(TableColumnRelate.id, TableColumnRelate.db_id, TableColumnRelate.table_id,
                            DimColumn.column_name, DimColumn.column_describe, DimColumn.data_type_id,
                            DimColumn.data_length, DimDataType.data_type_name). \
                filter(TableColumnRelate.db_id == db_id).filter(TableColumnRelate.table_id == table_id).\
                filter(TableColumnRelate.is_valid == 1).all()

        else:
            table_info = TableColumnRelate.query.join(DimColumn, DimColumn.id == TableColumnRelate.column_id). \
                join(DimDataType, DimDataType.id == DimColumn.data_type_id). \
                add_columns(TableColumnRelate.id, TableColumnRelate.db_id, TableColumnRelate.table_id,
                            DimColumn.column_name, DimColumn.column_describe, DimColumn.data_type_id,
                            DimColumn.data_length, DimDataType.data_type_name).\
                filter(TableColumnRelate.table_id == table_id). \
                filter(TableColumnRelate.is_valid == 1).all()

        arr = [{
            'id': data.id,
            'db_id': data.db_id,
            'table_id': data.table_id,
            'column_name': data.column_name,
            'data_type_id': data.data_type_id,
            'data_type_name': data.data_type_name,
            'data_length': data.data_length,
            'column_describe': data.column_describe
        } for data in table_info]

        return arr


# 用户管理
def read_user():
    table_info = User.query.join(Role, Role.id == User.role_id).\
        add_columns(User.id, User.email, User.name.label('username'), User.role_id, Role.name.label('role_name'),
                    User.registered_on).\
        filter(User.is_valid == 1).all()

    if table_info:
        return [{
            'id': data.id,
            'email': data.email,
            'name': data.username,
            'role_id': data.role_id,
            'role_name': data.role_name,
            'registered_on': data.registered_on
        } for data in table_info]


# get_mapping
def get_mapping(table_id):
    query_info = MappingInfo.query.join(DimColumn, DimColumn.id == MappingInfo.column_id). \
        join(DbTableRelate, DbTableRelate.id == MappingInfo.table_id). \
        join(DimDb, DimDb.id == MappingInfo.db_id). \
        join(User, User.id == MappingInfo.owner_id). \
        add_columns(MappingInfo.db_id, DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name,
                    DbTableRelate.table_describe, MappingInfo.column_id, DimColumn.column_name,
                    DimColumn.column_describe, MappingInfo.target_db,
                    MappingInfo.target_table, MappingInfo.target_column, MappingInfo.owner_id, User.name).\
        filter(MappingInfo.table_id == table_id).all()
    if query_info:
        return [{
            'db_name': data.db_name,
            'table_describe': data.table_describe,
            'table_name': data.table_name,
            'column_name': data.column_name,
            'column_describe': data.column_describe,

            'source_db': data.target_db,
            'source_table': data.target_table,
            'source_column': data.target_column,
            'owner_name': data.name
        } for data in query_info], False
    return [], False

# search mapping
def search_mapping(search_type, search_text):
    query_info = MappingInfo.query.join(DimColumn, DimColumn.id == MappingInfo.column_id). \
        join(DbTableRelate, DbTableRelate.id == MappingInfo.table_id). \
        join(DimDb, DimDb.id == MappingInfo.db_id). \
        join(User, User.id == MappingInfo.owner_id). \
        add_columns(MappingInfo.db_id, DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name,
                    DbTableRelate.table_describe, MappingInfo.column_id, DimColumn.column_name,
                    DimColumn.column_describe, MappingInfo.target_db,
                    MappingInfo.target_table, MappingInfo.target_column, MappingInfo.owner_id, User.name)

    search_str = '%{}%'.format(search_text)
    # 表名
    if search_type == 1:
        query_info = MappingInfo.query.join(DbTableRelate, DbTableRelate.id == MappingInfo.table_id). \
            join(DimDb, DimDb.id == MappingInfo.db_id). \
            add_columns(DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name, DbTableRelate.table_describe). \
            with_entities(DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name,
                          DbTableRelate.table_describe).distinct().\
            filter(DbTableRelate.table_name.like(search_str))

    # 表描述
    if search_type == 2:
        query_info = MappingInfo.query.join(DbTableRelate, DbTableRelate.id == MappingInfo.table_id). \
            join(DimDb, DimDb.id == MappingInfo.db_id). \
            add_columns(DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name, DbTableRelate.table_describe). \
            with_entities(DimDb.db_name, MappingInfo.table_id, DbTableRelate.table_name,
                          DbTableRelate.table_describe).distinct(). \
            filter(DbTableRelate.table_describe.like(search_str))
    # 列名
    if search_type == 3:
        query_info = query_info.filter(DimColumn.column_name.like(search_str))
    # 表描述
    if search_type == 4:
        query_info = query_info.filter(DimColumn.column_describe.like(search_str))

    table_info = query_info.all()
    if table_info:
        if search_type in [1, 2]:
            return [{
                'db_name': data.db_name,
                'table_id': data.table_id,
                'table_name': data.table_name,
                'table_describe': data.table_describe
            } for data in table_info], True
        else:
            return [{
                'db_name': data.db_name,
                'table_describe': data.table_describe,
                'table_name': data.table_name,
                'column_name': data.column_name,
                'column_describe': data.column_describe,

                'source_db': data.target_db,
                'source_table': data.target_table,
                'source_column': data.target_column,
                'owner_name': data.name
            } for data in table_info], False
    return [], False
